package com.example.mybatis.utils;

import com.example.mybatis.model.ColumnInfo;
import com.example.mybatis.model.SheetInfo;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
//import org.apache.poi.ss.usermodel.FontFamily;
//import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.StringUtils;
//import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColor;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelReadWriteUtil {
    private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
    /**
     * poi导出excel工具类
     * @param sheetInfoMap SheetInfo对象的键值对集合
     * @param dataMap 泛型数据键值对集合 dataMap与sheetInfoMap的键必须一致
     * @param os 输出流
     * @throws Exception
     */
    public static void writeExcel(Map<String,SheetInfo> sheetInfoMap, Map<String,List<?>> dataMap, OutputStream os)throws Exception{
        XSSFWorkbook workbook = new XSSFWorkbook();
        Set<String> sheetKeys = sheetInfoMap.keySet();
        for(String sheetName: sheetKeys){
            SheetInfo sheetInfo = sheetInfoMap.get(sheetName);
            String voClass = sheetInfo.getVoClass();
            //更具sheet名创建sheet
            XSSFSheet sheet = workbook.createSheet(sheetName);
            //创建页头
            XSSFRow headRow = sheet.createRow(0);
            List<ColumnInfo> columns = sheetInfo.getColumns();
            //创建表头样式
//          XSSFCellStyle headCellStyle = workbook.createCellStyle();
            for(int i=0;i<columns.size();i++){
                ColumnInfo columnInfo = columns.get(i);
                XSSFCell cell = null;
                cell = headRow.createCell(i, CellType.STRING);
                cell.setCellValue(columnInfo.getDisplayName());
            }

            Class clazz = Class.forName(voClass);
            List<?> data = dataMap.get(sheetName);
            //创建表格内容单元格样式
            for(int i=0;i<data.size();i++){
                XSSFRow row = sheet.createRow(i+1);
                for(int j=0;j<columns.size();j++){
                    ColumnInfo columnInfo = columns.get(j);
                    String fieldName = columnInfo.getName();
                    //通过执行反射方法获取对应的字段值
                    String methodName = "get"+upCaseFirstChar(fieldName);
                    Method method = clazz.getDeclaredMethod(methodName,new Class[]{});
                    Object obj = method.invoke(data.get(i),new Object[]{});
                    XSSFCell contentCell = row.createCell(j,CellType.STRING);
                    String value = obj.toString();
                    contentCell.setCellValue(value);
                }

            }
            //将工作簿写到输出流中
            workbook.write(os);
            workbook.close();
        }

    }

    public static void writeSingleExcel(SheetInfo sheetInfo,List<?> dataList,OutputStream os) throws Exception{
        XSSFWorkbook workbook = new XSSFWorkbook();
        String sheetName = sheetInfo.getName();
        String voClass = sheetInfo.getVoClass();
        XSSFSheet sheet = workbook.createSheet(sheetName);
        Row headRow = sheet.createRow(0);
        //设置行高
//        headRow.setHeightInPoints(256*30);
        //设置列头
        List<ColumnInfo> columnInfos = sheetInfo.getColumns();
        for(int i=0;i<columnInfos.size();i++){
            Cell cell = headRow.createCell(i,CellType.STRING);
            ColumnInfo columnInfo = columnInfos.get(i);
//            sheet.setColumnWidth(i,columnInfo.getWidth());
            cell.setCellValue(columnInfo.getDisplayName());
        }
        //设置列主体
        Class clazz = Class.forName(voClass);
        for(int i=0;i<dataList.size();i++){
            Object item = dataList.get(i);
            Row contentRow = sheet.createRow(i+1);
//            contentRow.setHeightInPoints(256*26);
            for(int j=0;j<columnInfos.size();j++){
                ColumnInfo columnInfo = columnInfos.get(j);
//                Integer width = columnInfo.getWidth();
                String name = columnInfo.getName();
                String getFieldName = "get"+upCaseFirstChar(name);
                Method method = clazz.getDeclaredMethod(getFieldName,null);
                Object value = method.invoke(item,null);
                String type = columnInfo.getType();
                switch (type){
                    case "Integer":
                        Integer intVal = (Integer) value;
                        Cell cell = contentRow.createCell(j,CellType.NUMERIC);
                        double val = Double.parseDouble(String.valueOf(intVal));
                        cell.setCellValue(val);
                        break;
                    case "Long":
                        Long longVal = (Long) value;
                        Cell cell1 = contentRow.createCell(j,CellType.NUMERIC);
                        double val1 = Double.parseDouble(String.valueOf(longVal));
                        cell1.setCellValue(val1);
                        break;
                    case "Date":
                        String dateStr = (String) value;
                        Cell cell2 = contentRow.createCell(j,CellType.STRING);
                        cell2.setCellValue(dateStr);
                    default:
                        String strVal = (String) value;
                        Cell cell3 = contentRow.createCell(j,CellType.STRING);
                        cell3.setCellValue(strVal);
                        break;
                }
            }
        }
        //将工作簿写到输出流中
        workbook.write(os);
        workbook.close();

    }

    /**
     *读取Excel中表格数据工具类
     * @param sheetInfoMap
     * @param file
     * @param isExcel2003
     * @return
     * @throws Exception
     */
    public static Map<String,List<?>> readExcel(Map<String,SheetInfo> sheetInfoMap, File file, boolean isExcel2003) throws Exception{
        Map<String,List<?>> dataMap = new HashMap<>();
        InputStream is = new FileInputStream(file);
        Workbook workbook = null;
        if(isExcel2003){ //如果文件后缀为.xls,则使用HSSFWorkbook类解析文件
            workbook = new HSSFWorkbook(is);
        }else{  //默认使用XSSFWorkbook类
            workbook = new XSSFWorkbook(is);
        }
        Set<String> sheetNames = sheetInfoMap.keySet();
        for(String sheetName: sheetNames){
            Sheet sheet = workbook.getSheet(sheetName);
            int rowNumbers = sheet.getPhysicalNumberOfRows();
            SheetInfo sheetInfo = sheetInfoMap.get(sheetName);
            List<ColumnInfo> columns = sheetInfo.getColumns();
            Class clazz = Class.forName(sheetInfo.getVoClass());
            List<Object> list = new ArrayList<>();
            for(int i=1;i<rowNumbers;i++){
                Object instance = clazz.newInstance();
                Row contentRow = sheet.getRow(i);
                for(int j=0;j<columns.size();j++){
                    ColumnInfo columnInfo = columns.get(j);
                    String fieldName = columnInfo.getName();
                    String setMethodName = "set"+upCaseFirstChar(fieldName);
                    Field field = clazz.getDeclaredField(fieldName);
                    Class fieldClass = field.getType();
                    Method method = clazz.getDeclaredMethod(setMethodName,fieldClass);
                    Cell cell = contentRow.getCell(j);
                    switch (columnInfo.getType()){
                        case "String":
                            String value = cell.getStringCellValue();
                            method.invoke(instance,value);
                            break;
                        case "Integer":
                            String doubleStr = String.valueOf(cell.getNumericCellValue());
                            String intStr = doubleStr.substring(0,doubleStr.indexOf('.'));
                            Integer intVal = Integer.valueOf(intStr);
                            if("String".equals(fieldClass.getSimpleName())){
                                method.invoke(instance,intStr);
                            }else{
                                method.invoke(instance,intVal);
                            }
                            break;
                        case "Long":
                            String phoneStr = cell.getStringCellValue();
                            if(!StringUtils.isEmpty(phoneStr)){
                                Long longVal = Long.valueOf(phoneStr);
                                method.invoke(instance,longVal);
                            }
                            break;
                        case "Date":
                            Date birthDate = cell.getDateCellValue();
                            if(birthDate!=null){
                                String dateVal = sdf.format(birthDate);
                                method.invoke(instance,dateVal);
                            }
                            break;
                        default:
                            break;
                    }
                }
                list.add(instance);
            }
            dataMap.put(sheetName,list);
        }
        return dataMap;

    }


    public static String upCaseFirstChar(String filedName){
        char[] chs = filedName.toCharArray();
        chs[0]= (char) (chs[0]-32);
        return new String(chs);
    }

}
